﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class ProjectGateway : DBGateway
    {
        public bool HasThisProjectAllreadyExist(Project project)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select ProjectName from tblProjects where ProjectName='" + project.ProjectName + "'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                ConnectionObj.Close();
            }
            return false;
        }


        public string SaveProject(Project project)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("insert into tblProjects Values(@ProjectName,@startingDate,@ClosingDate,@TotalEmployee,@ProjectDescription,@Location)");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@ProjectName", SqlDbType.VarChar).Value = project.ProjectName;
                CommandObj.Parameters.Add("@startingDate", SqlDbType.DateTime).Value = project.startingDate;
                CommandObj.Parameters.Add("@ClosingDate", SqlDbType.DateTime).Value = project.ClosingDate;
                CommandObj.Parameters.Add("@TotalEmployee", SqlDbType.Int).Value = project.TotalEmployee;
                CommandObj.Parameters.Add("@ProjectDescription", SqlDbType.VarChar).Value = project.ProjectDescription;
                CommandObj.Parameters.Add("@Location", SqlDbType.VarChar).Value = project.Location;
                CommandObj.ExecuteNonQuery();
                return "Project Saved Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error Ocured during Saving Project, Please Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public string UpdateProject(Project project)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Update tblProjects set ProjectName=@ProjectName,startingDate=@startingDate," +
                                                       "ClosingDate=@ClosingDate,TotalEmployee=@TotalEmployee,ProjectDescription=@ProjectDescription,Location=@Location where ProjectID='" + project.Id + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@ProjectName", SqlDbType.VarChar).Value = project.ProjectName;
                CommandObj.Parameters.Add("@startingDate", SqlDbType.DateTime).Value = project.startingDate;
                CommandObj.Parameters.Add("@ClosingDate", SqlDbType.DateTime).Value = project.ClosingDate;
                CommandObj.Parameters.Add("@TotalEmployee", SqlDbType.Int).Value = project.TotalEmployee;
                CommandObj.Parameters.Add("@ProjectDescription", SqlDbType.VarChar).Value = project.ProjectDescription;
                CommandObj.Parameters.Add("@Location", SqlDbType.VarChar).Value = project.Location;
                CommandObj.ExecuteNonQuery();
                return "Project Updated Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error Ocured during Updating Project, Please Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Project> GetAllProjects()
        {
            try
            {
                ConnectionObj.Open();
                List<Project> projects=new List<Project>();
                CommandObj.CommandText = @"select * from tblProjects order by ProjectName";
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Project project=new Project();
                    project.Id = Convert.ToInt32(reader[0]);
                    project.ProjectName = reader[1].ToString();
                    project.startingDate = Convert.ToDateTime(reader[2]);
                    project.ClosingDate = Convert.ToDateTime(reader[3]);
                    project.TotalEmployee = Convert.ToInt32(reader[4]);
                    project.ProjectDescription = reader[5].ToString();
                    project.Location = reader[6].ToString();
                    projects.Add(project);
                }
                return projects;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during reading Data in Data base");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }


        public string DeleteProject(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"Delete from tblProjects where ProjectID='"+id+"'";
                CommandObj.ExecuteNonQuery();
                return "Project Deleted Successfully";
            }
            catch (Exception)
            {
                throw new Exception("Error occured during Deleting Project");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Project> GetProjects(int empId)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"SELECT tblProjects.ProjectID,tblProjects.ProjectName FROM tblProjects INNER JOIN
                                        tblProjectManager ON tblProjects.ProjectID = tblProjectManager.ProjectId WHERE (tblProjectManager.EmpId = '"+empId+"')");
                SqlDataReader reader = CommandObj.ExecuteReader();
                List<Project>projects=new List<Project>();
                while (reader.Read())
                {
                    Project project=new Project();
                    project.Id = Convert.ToInt32(reader[0]);
                    project.ProjectName = reader[1].ToString();
                    projects.Add(project);
                }
                return projects;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured during reading Project Information");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public int GetProjectManagerId(int id)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = @"select Id from tblProjectManager where EmpId='"+id+"'";
                SqlDataReader reader = CommandObj.ExecuteReader();
                int pmId = 0;
                while (reader.Read())
                {
                    pmId = Convert.ToInt32(reader[0]);
                }
                return pmId;
            }
            catch (Exception)
            {
                throw new Exception("Error occured in Database !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}